package com.DreamTeam.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.DreamTeam.dao.IStatisticalAnalysis;
import com.DreamTeam.po.OrderMaster;
import com.DreamTeam.vo.StaAnalCustomer;
import com.DreamTeam.vo.StaAnalEmployee;
import com.DreamTeam.vo.StaAnalProductClass;

public class StatisticalAnalysis implements IStatisticalAnalysis {
	private Connection conn;
	public StatisticalAnalysis(Connection conn){
		this.conn=conn;
	}

	@Override
	public List<StaAnalEmployee> staAnalByEmployee() {
		String sql = "SELECT a.employeeNo,a.employeeName,COUNT(b.EmployeeNo) " +
				"orderQty,DATE_FORMAT(orderDate,'%Y-%m') AS orderDate," +
				"SUM(orderSum) FROM employee a,orderMaster b WHERE a.employeeNo=b.employeeNo " +
				"AND orderDate GROUP BY employeeNo,DATE_FORMAT(orderDate,'%Y-%m') " +
				"ORDER BY orderDate ASC";
		PreparedStatement prsmt=null;
		List<StaAnalEmployee> list= new ArrayList();
		try {
			prsmt = conn.prepareStatement(sql);
			ResultSet re = prsmt.executeQuery();
			while(re.next()){
				StaAnalEmployee se = new StaAnalEmployee();
				se.setEmployeeNo(re.getString(1));
				se.setEmployeeName(re.getString(2));
				se.setOrderQty(re.getInt(3));
				se.setOrderDate(re.getString(4));
				se.setSumPrice(re.getDouble(5));
				list.add(se);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<StaAnalCustomer> staAnalByCustomer() {
		String sql = "SELECT a.CustomerNo,a.CustomerName,DATE_FORMAT(orderDate,'%Y-%m') AS" +
				" orderDate,COUNT(b.CustomerNo),SUM(orderSum) FROM customer a,orderMaster b" +
				" WHERE a.customerNo = b.customerNo GROUP BY customerNo ," +
				"DATE_FORMAT(orderDate,'%Y-%m') ORDER BY orderDate ASC";
		PreparedStatement prsmt = null;
		List<StaAnalCustomer> list = new ArrayList();
		try {
			prsmt = conn.prepareStatement(sql);
			ResultSet re = prsmt.executeQuery();
			while(re.next()){
				StaAnalCustomer sc = new StaAnalCustomer();
				sc.setCustomerNo(re.getString(1));
				sc.setCustomerName(re.getString(2));
				sc.setOrderDate(re.getString(3));
				sc.setQty(re.getInt(4));
				sc.setSumPrice(re.getDouble(5));
				list.add(sc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<StaAnalProductClass> staAnalByProductClass() {
		String sql = "SELECT a.productNo,productName ,productClass," +
				"DATE_FORMAT(orderDate,'%Y-%m') AS orderDate ,COUNT(b.ProductNo)," +
				"(COUNT(b.ProductNo)*a.ProductPrice) FROM product a,orderDetail b ," +
				"orderMaster c WHERE a.ProductNo=b.ProductNo AND b.OrderNo = c.OrderNo " +
				"GROUP BY b.productNo,DATE_FORMAT(orderDate,'%Y-%m') ORDER BY orderDate ASC";
		PreparedStatement prsmt = null;
		List<StaAnalProductClass> list = new ArrayList();
		try {
			prsmt = conn.prepareStatement(sql);
			ResultSet re = prsmt.executeQuery();
			while(re.next()){
				StaAnalProductClass sp = new StaAnalProductClass();
				sp.setProductNo(re.getString(1));
				sp.setProductName(re.getString(2));
				sp.setProductClass(re.getString(3));
				sp.setOrderDate(re.getString(4));
				sp.setSaleQty(re.getInt(5));
				sp.setSumPrice(re.getDouble(6));
				list.add(sp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public List<OrderMaster> orderByPrice(double low, double high) {
		String sql = "select * from orderMaster where orderSum between "+low+" and "+high;
		List<OrderMaster> list = new ArrayList();
		PreparedStatement prsmt = null;
		try {
			prsmt = conn.prepareStatement(sql);
			ResultSet re = prsmt.executeQuery();
			while(re.next()){
				OrderMaster om = new OrderMaster();
				om.setOrderNo(re.getString(1));
				om.setCustomerNo(re.getString(2));
				om.setEmployeeNo(re.getString(3));
				om.setOrderDate(re.getDate(4));
				om.setOrderSum(re.getDouble(5));
				om.setInvoiceNo(re.getString(6));
				list.add(om);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	

}
